Internal Storage, Sorting, and Indexing Behavior of BLOB and TEXT
MySQL stores BLOB and TEXT values differently from normal string or numeric columns. Their size and flexible length affect how MySQL sorts and indexes them.
Both BLOB and TEXT are stored off-page when values are large, with only a pointer kept in the table row.
Small values may be stored inline depending on the storage engine (InnoDB stores first 768 bytes inline).
BLOB stores binary data with no character set or collation.
TEXT stores character data and supports collation-based sorting.
TEXT sorting uses character set and collation rules, which may be slower due to case and accent handling.
BLOB sorting is byte-by-byte (binary sorting), without collation or case-insensitivity.
Sorting large TEXT/BLOB values is slower because MySQL may need to fetch off-page data.
TEXT and BLOB columns cannot be fully indexed unless a prefix length is specified.
MySQL requires something like
INDEX(column(100))for indexing.Only the specified prefix (e.g., first 100 characters) is included in the index.
Full indexing is not allowed because values may be very large and variable-length.
Indexing large TEXT/BLOB columns increases storage and reduces performance.
TEXT has collation-aware comparisons; BLOB does not.
Prefix indexing helps performance but limits exact matching for long values.
Full-text indexing (FULLTEXT INDEX) is recommended for large text search instead of regular indexes.